package org.zoomdev.zoom.dao.ddl.utils;

import org.zoomdev.zoom.dao.meta.ColumnMeta;
import org.zoomdev.zoom.dao.meta.IndexMeta;
import org.zoomdev.zoom.dao.meta.TableMeta;

import java.util.*;
import java.util.stream.Collectors;

import static org.zoomdev.zoom.dao.utils.SqlUtils.escapeSql;

public final class MetaIncrement {


    /**
     * 比较从class解析出来的TableMeta，与数据库解析出来的TableMeta，得到增量修改sql语句
     *
     * @param fromClass    class解析出来的TableMeta
     * @param fromDatabase 数据库解析出来的TableMeta
     * @return 增量修改SQL列表
     */
    public static List<String> getIncrements(TableMeta fromClass, TableMeta fromDatabase) {
        List<String> incrementSqlList = new ArrayList<>();

        // 1. 检查并生成表属性变更SQL
        generateTableAlterSql(fromClass, fromDatabase, incrementSqlList);

        // 2. 检查并生成列变更SQL
        generateColumnAlterSql(fromClass, fromDatabase, incrementSqlList);

        // 3. 检查并生成主键变更SQL
        generatePrimaryKeyAlterSql(fromClass, fromDatabase, incrementSqlList);

        // 4. 检查并生成索引变更SQL
        generateIndexAlterSql(fromClass, fromDatabase, incrementSqlList);

        return incrementSqlList;
    }

    private static void generateTableAlterSql(TableMeta fromClass, TableMeta fromDatabase,
                                              List<String> sqlList) {

        // 引擎变更
        if (!Objects.equals(fromClass.getEngine(), fromDatabase.getEngine())
                && fromClass.getEngine() != null && !fromClass.getEngine().isEmpty()) {
            sqlList.add(String.format(
                    "ALTER TABLE `%s` ENGINE = %s;",
                    escapeSql(fromClass.getName()),
                    fromClass.getEngine()
            ));
        }

        // 表注释变更
        if (!Objects.equals(fromClass.getComment(), fromDatabase.getComment())) {
            sqlList.add(String.format(
                    "ALTER TABLE `%s` COMMENT = '%s';",
                    escapeSql(fromClass.getName()),
                    escapeSql(fromClass.getComment())
            ));
        }

    }

    private static void generateColumnAlterSql(TableMeta fromClass, TableMeta fromDatabase,
                                               List<String> sqlList) {
        Map<String, ColumnMeta> dbColumnMap = new HashMap<>();
        for (ColumnMeta column : fromDatabase.getColumns()) {
            dbColumnMap.put(column.getName(), column);
        }

        // 按顺序处理类中的列
        for (ColumnMeta classColumn : fromClass.getColumns()) {
            ColumnMeta dbColumn = dbColumnMap.get(classColumn.getName());

            if (dbColumn == null) {
                // 添加新列
                sqlList.add(generateAddColumnSql(fromClass.getName(), classColumn));
            } else {
                // 比较列定义
                if (!columnEquals(classColumn, dbColumn)) {
                    sqlList.add(generateModifyColumnSql(fromClass.getName(), classColumn));
                }
            }
        }

        // 检查需要删除的列（谨慎使用）
        /*
        Set<String> classColumnNames = Arrays.stream(fromClass.getColumns())
                .map(ColumnMeta::getName)
                .collect(Collectors.toSet());

        for (ColumnMeta dbColumn : fromDatabase.getColumns()) {
            if (!classColumnNames.contains(dbColumn.getName())) {
                sqlList.add(String.format(
                    "ALTER TABLE `%s` DROP COLUMN `%s`;", // 注意：删除列可能导致数据丢失
                    escapeSql(fromClass.getName()),
                    escapeSql(dbColumn.getName())
                ));
            }
        }
        */
    }

    private static String generateAddColumnSql(String tableName, ColumnMeta column) {
        return String.format(
                "ALTER TABLE `%s` ADD COLUMN %s;",
                escapeSql(tableName),
                getColumnDefinition(column)
        );
    }

    private static String generateModifyColumnSql(String tableName, ColumnMeta column) {
        return String.format(
                "ALTER TABLE `%s` MODIFY COLUMN %s;",
                escapeSql(tableName),
                getColumnDefinition(column)
        );
    }

    private static String getColumnDefinition(ColumnMeta column) {
        StringBuilder sb = new StringBuilder();
        column.toSql(sb, null, false);
        return sb.toString();
    }

    private static void generatePrimaryKeyAlterSql(TableMeta fromClass, TableMeta fromDatabase,
                                                   List<String> sqlList) {
        String tableName = fromClass.getName();
        String[] classPKs = fromClass.getPrimaryKeys();
        String[] dbPKs = fromDatabase.getPrimaryKeys();

        // 检查主键是否相同
        boolean pkChanged = !Arrays.equals(sorted(classPKs), sorted(dbPKs));

        if (pkChanged) {
            // 先删除旧主键
            if (dbPKs != null && dbPKs.length > 0) {
                sqlList.add(String.format(
                        "ALTER TABLE `%s` DROP PRIMARY KEY;",
                        escapeSql(tableName)
                ));
            }

            // 添加新主键
            if (classPKs != null && classPKs.length > 0) {
                String columns = Arrays.stream(classPKs)
                        .map(col -> "`" + escapeSql(col) + "`")
                        .collect(Collectors.joining(", "));

                sqlList.add(String.format(
                        "ALTER TABLE `%s` ADD PRIMARY KEY (%s);",
                        escapeSql(tableName),
                        columns
                ));
            }
        }
    }

    private static void generateIndexAlterSql(TableMeta fromClass, TableMeta fromDatabase,
                                              List<String> sqlList) {
        String tableName = fromClass.getName();

        // 构建数据库索引的签名映射
        Map<String, String> dbIndexSignatures = new HashMap<>();
        if (fromDatabase.getIndexes() != null) {
            for (IndexMeta index : fromDatabase.getIndexes()) {
                dbIndexSignatures.put(index.getName(), getIndexSignature(index));
            }
        }

        // 处理类中定义的索引
        if (fromClass.getIndexes() != null) {
            for (IndexMeta classIndex : fromClass.getIndexes()) {
                String signature = getIndexSignature(classIndex);
                String dbSignature = dbIndexSignatures.get(classIndex.getName());

                if (dbSignature == null) {
                    // 添加新索引
                    sqlList.add(generateCreateIndexSql(tableName, classIndex));
                } else if (!signature.equals(dbSignature)) {
                    // 先删除旧索引
                    sqlList.add(generateDropIndexSql(tableName, classIndex.getName()));

                    // 添加新索引
                    sqlList.add(generateCreateIndexSql(tableName, classIndex));
                }
            }
        }

        // 删除不再需要的索引（谨慎使用）
        /*
        Set<String> classIndexNames = fromClass.getIndexes() == null ?
            Set.of() :
            Arrays.stream(fromClass.getIndexes())
                .map(IndexMeta::getName)
                .collect(Collectors.toSet());

        for (String indexName : dbIndexSignatures.keySet()) {
            if (!classIndexNames.contains(indexName)) {
                sqlList.add(generateDropIndexSql(tableName, indexName));
            }
        }
        */
    }

    private static String generateCreateIndexSql(String tableName, IndexMeta index) {
        String columns = Arrays.stream(index.getColumns())
                .map(col -> "`" + escapeSql(col) + "`")
                .collect(Collectors.joining(", "));

        if (index.isUnique()) {
            return String.format(
                    "CREATE UNIQUE INDEX `%s` ON `%s` (%s);",
                    escapeSql(index.getName()),
                    escapeSql(tableName),
                    columns
            );
        } else {
            return String.format(
                    "CREATE INDEX `%s` ON `%s` (%s);",
                    escapeSql(index.getName()),
                    escapeSql(tableName),
                    columns
            );
        }
    }

    private static String generateDropIndexSql(String tableName, String indexName) {
        return String.format(
                "DROP INDEX `%s` ON `%s`;",
                escapeSql(indexName),
                escapeSql(tableName)
        );
    }

    private static String getIndexSignature(IndexMeta index) {
        return (index.isUnique() ? "U" : "I") + ":" +
                Arrays.stream(index.getColumns())
                        .sorted()
                        .collect(Collectors.joining(","));
    }

    private static boolean columnEquals(ColumnMeta c1, ColumnMeta c2) {
        return Objects.equals(c1.getName(), c2.getName()) &&
                Objects.equals(c1.getComment(), c2.getComment()) &&
                c1.getType() == c2.getType() &&
                c1.getSize() == c2.getSize() &&
                c1.getScale() == c2.getScale() &&
                c1.isNotNull() == c2.isNotNull() &&
                c1.isAutoIncrement() == c2.isAutoIncrement() &&
                Arrays.equals(c1.getValues(), c2.getValues()) &&
                Objects.equals(getDefaultValueString(c1), getDefaultValueString(c2));
    }

    private static String getDefaultValueString(ColumnMeta column) {
        String dv = column.getDefaultValue();
        if (dv == null) return "";
        return dv;
    }

    private static String[] sorted(String[] array) {
        if (array == null) return null;
        String[] sorted = Arrays.copyOf(array, array.length);
        Arrays.sort(sorted);
        return sorted;
    }


}